package com.xiguaji.tiktok.v1.ins.product.repository;

import com.xiguaji.tiktok.v1.ins.product.dto.*;
import com.xiguaji.tiktok.v1.ins.product.model.InsProduct;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface InsProductRepository extends CrudRepository<InsProduct,Long>, InsProductExtRepository {

    @Query("select p.*,b.username,b.full_name,b.pic_url,b.is_verified from ins_product p left join ins_blogger b on p.merchant_uid = b.uid where p.product_id = :productId")
    InsProductData findByProductDataByProductId(long productId);


    /**
     * 商品统计 达人数、作品数、参与数、观看数
     * @param productId
     * @param startTime
     * @param endTime
     */
    @Query("select IFNULL((select count(*) from (select distinct mp.uid from ins_rel_media_product mp where mp.product_id = :productId and mp.taken_time between :startTime and :endTime) c ),0) influencer" +
            ",count(*) posts,IFNULL(sum(m.like_count+m.comment_count),0) engagements,IFNULL(sum(m.video_view_count),0) views " +
            "from ins_rel_media_product mp left join ins_media m on mp.shortcode = m.shortcode " +
            "where mp.product_id = :productId and mp.taken_time between :startTime and :endTime")
    InsProductStatistics getInsMediaProductDataCountByTakenTime(long productId, long startTime, long endTime);


    /**
     * 该商品的发帖达人数走势
     * @param productId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select count(*) influencer,c.taken_date " +
            "from (select distinct mp.uid,FROM_UNIXTIME(mp.taken_time,'%m-%d') taken_date  from ins_rel_media_product mp where mp.product_id = :productId and mp.taken_time between :startTime and :endTime) c " +
            "group by c.taken_date order by c.taken_date")
    List<InsProductTrend> getProductBloggerCount(long productId, long startTime, long endTime);


    /**
     * 该商品的发帖帖子数走势
     * @param productId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  count(*) media,FROM_UNIXTIME(mp.taken_time,'%m-%d') taken_date  " +
            "from ins_rel_media_product mp where mp.product_id = :productId and mp.taken_time between :startTime and :endTime group by taken_date order by mp.taken_time")
    List<InsProductTrend> getProductMediaCount(long productId, long startTime, long endTime);


    /**
     * 统计博主下分类的商品数量
     * @param uid
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select count(*) count,p.overall_category_name category from " +
            "ins_rel_media_product mp left join ins_product p on mp.product_id = p.product_id " +
            "where mp.uid = :uid and mp.taken_time between :startTime and :endTime group by p.overall_category_name order by count desc")
    List<InsProductCategoryStatistics> getCountCategory(long uid, long startTime, long endTime);


    @Query("select last_st_time from ins_st_product_base where product_id = :productId and date_type = 0")
    Long getLastStTime(long productId);


    /*@Query("select p.*,b.username,b.full_name,b.is_verified,b.pic_url from ins_rel_media_product mp inner join ins_product p on mp.product_id = p.product_id left join ins_blogger_main b on p.merchant_uid = b.uid where mp.shortcode = :shortCode order by mp.er desc")
    List<InsProductData> getProductByMediaShortCode(String shortCode,int page,int pageSize);*/
    




}
